--客户基础数据代码开发
with year_customer_sum as(
select
customer_code,                                      --客户编码
max(customer_name) as customer_name,                --客户名字
concat_ws(',',collect_set(ordersource_code)) as ordersource_code,                                   --平台code
ordersource_name,
sum(day_customer_sum)  as year_customer_sum,        --客户一年运单量
sum(nvl(day_customer_weight_sum,0)) as year_customer_weight_sum   --客户一年运单总重
from jms_dm.dm_customer_base_detail_dt
  where dt >=trunc('{{ execution_date | cst_ds }}','YEAR')
  and dt<='{{ execution_date | cst_ds }}'
  and day_customer_sum>0
  group by customer_code,ordersource_name
),
last_month_data as(
  select
    customer_code,                                                       --客户编码
    max(customer_name) as customer_name,                                 --客户名字
    concat_ws(',',collect_set(ordersource_code)) as ordersource_code,    --平台ID
    ordersource_name,                                                    --平台名字
    max(customer_type_id) as customer_type_id,
    max(customer_type_name) as customer_type_name,
    max(customer_category)as customer_category,
    max(customer_category_name) as customer_category_name,
    max(customer_attribute_id) as customer_attribute_id,
    max(customer_attribute_name) as customer_attribute_name,
    max(subordinate_agent_name) as subordinate_agent_name,
    max(subordinate_agent_code) as subordinate_agent_code,
    max(first_franchisee_id) as first_franchisee_id,
    max(first_franchisee_code) as first_franchisee_code,
    max(first_franchisee_name) as first_franchisee_name,
    max(customer_belong_network_id) as customer_belong_network_id,
    max(customer_belong_network_name) as customer_belong_network_name,
    max(customer_province_id) as customer_province_id,
    max(customer_province_desc) as customer_province_desc,
    max(customer_city_id) as customer_city_id,
    max(customer_city_desc) as customer_city_desc,
    max(customer_area_id) as customer_area_id,
    max(customer_area_desc) as customer_area_desc,
    sum(nvl(day_customer_sum,0)) as month_customer_waybill_sum,   --客户运单总量
         sum(day_customer_weight_sum) as month_customer_weight_sum,
  min(customer_signed_time) as customer_signed_time
  from jms_dm.dm_customer_base_detail_dt
  where dt >=trunc(date_add('{{ execution_date | cst_ds }}',-day('{{ execution_date | cst_ds }}')),'MM')
  and dt< trunc('{{ execution_date | cst_ds }}','MM')
  and day_customer_sum>0
  group by customer_code,ordersource_name
  ),
day_customer_sum as(
select
customer_code,                                                   --客户编码
max(customer_name) as customer_name,                             --客户名字
   concat_ws(',',collect_set(ordersource_code)) as ordersource_code,  --平台code
    ordersource_name,                              --平台
max(day_customer_sum) as day_customer_sum,                       --客户日运单量
sum(nvl(day_customer_weight_sum,0)) as day_customer_weight_sum   --客户一年运单总重
from jms_dm.dm_customer_base_detail_dt a
  where dt ='{{ execution_date | cst_ds }}'
  and a.day_customer_sum>0
  group by customer_code,ordersource_name
)
insert overwrite table jms_dm.dm_customer_base_data_dt partition(dt)
   select
      a.customer_code,                                                            --客户编码
      a.customer_name,                                      --客户名字
     a.customer_level,                        --客户等级
      a.ordersource_code,                                          --平台ID
      a.ordersource_name,                                          --平台名字
      a.customer_type_id  ,                                        -- 客户类型
      a.customer_type_name,                                        --客户类型名字
      a.customer_category,                                         --客户类别
      a.customer_category_name,
      a.customer_attribute_id ,                                    --客户属性ID
      a.customer_attribute_name,                                   --客户属性名字
      a.subordinate_agent_name,                                    --所属代理区
      --a.subordinate_agent_id,                                    --所属代理区ID
      a.subordinate_agent_code,                                    --所属代理区code
      a.first_franchisee_id    ,                                   -- 一级加盟商id',
      a.first_franchisee_code,                                     -- '一级加盟商code',
      a.first_franchisee_name  ,                                   -- 一级加盟商',
      a.customer_belong_network_id ,                               -- 客户所属网点ID
      a.customer_belong_network_name ,                             --  客户所属网点名字
      a.customer_province_id   ,                                   -- 客户所属省份id
      a.customer_province_desc ,                                   --客户所属省份名字
      a.customer_city_id     ,                                     -- 客户所属城市id
      a.customer_city_desc,                                        --  客户所属城市名字',
      a.customer_area_id   ,                                       --  客户所属区域id
      a.customer_area_desc ,                                       -- 客户所属区县的名字',
      a.month_customer_waybill_sum as month_customer_sum ,                                       --客户当月运单总量
      a.customer_signed_time,                                      --客户创建时间
      a.month_customer_weight_sum,                                 --客户月运单总重
      b.year_customer_sum,                                         --客户年运单量
      b.year_customer_weight_sum,                                  --客户年运单总重
      nvl(c.day_customer_sum,0) as day_customer_sum,                                          --客户日运单量
      nvl(c.day_customer_weight_sum,0) as day_customer_weight_sum,                                   --客户日运单总重
      case when is_new =1 and is_loss<> 1 and is_retrieve<>1 then 1
          when is_new<>1 and is_loss=1 and is_retrieve<>1 then 2
          when is_new<>1 and is_loss<>1 and is_retrieve=1 then 3
          else 4
          end as customer_stage,                           --当前客户状态
      '{{ execution_date | cst_ds }}' as dt2,
      '{{ execution_date | cst_ds }}' as dt     --分区日期
from (
select
      nvl(a.customer_code,b.customer_code) as customer_code,                                                     --客户编码
      nvl(a.customer_name,b.customer_name) as customer_name,                                                     --客户名字
      case when  nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >5000.0 then 'A'
            when nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >1000.0 then 'B'
            when nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >200.0 then 'C'
            when nvl(a.month_customer_waybill_sum,0) /day('{{ execution_date | cst_ds }}') >50.0 then 'D'
            else 'E' end  as customer_level,                                                                        --客户等级
      nvl(a.ordersource_code,b.ordersource_code) as ordersource_code,                                               --平台ID
      nvl(a.ordersource_name,b.ordersource_name) as ordersource_name,                                               --平台名字
      nvl(a.customer_type_id,b.customer_type_id) as customer_type_id  ,                                             -- 客户类型
      nvl(a.customer_type_name,b.customer_type_name) as customer_type_name,                                         --客户类型名字
      nvl(a.customer_category,b.customer_category) as customer_category,                                            --客户类别
      nvl(a.customer_category_name,b.customer_category_name) as customer_category_name,
      nvl(a.customer_attribute_id,b.customer_attribute_id) as customer_attribute_id ,                                --客户属性ID
      nvl(a.customer_attribute_name,b.customer_attribute_name) as customer_attribute_name,                           --客户属性名字
      nvl(a.subordinate_agent_name,b.subordinate_agent_name) as subordinate_agent_name,                              --所属代理区
      nvl(a.subordinate_agent_code,b.subordinate_agent_code) as subordinate_agent_code,                              --所属代理区code
      nvl(a.first_franchisee_id,b.first_franchisee_id) as first_franchisee_id    ,                                   -- 一级加盟商id',
      nvl(a.first_franchisee_code,b.first_franchisee_code) as first_franchisee_code,                                 -- '一级加盟商code',
      nvl(a.first_franchisee_name,b.first_franchisee_name) as first_franchisee_name  ,                               -- 一级加盟商',
      nvl(a.customer_belong_network_id,b.customer_belong_network_id) as customer_belong_network_id ,                 -- 客户所属网点ID
      nvl(a.customer_belong_network_name,b.customer_belong_network_name) as customer_belong_network_name ,           --  客户所属网点名字
      nvl(a.customer_province_id,b.customer_province_id) as customer_province_id   ,                                 -- 客户所属省份id
      nvl(a.customer_province_desc,b.customer_province_desc) as customer_province_desc ,                             --客户所属省份名字
      nvl(a.customer_city_id,b.customer_city_id) as customer_city_id     ,                                           -- 客户所属城市id
      nvl(a.customer_city_desc,b.customer_city_desc) as customer_city_desc,                                          --  客户所属城市名字',
      nvl(a.customer_area_id,b.customer_area_id) as customer_area_id   ,                                             --  客户所属区域id
      nvl(a.customer_area_desc,b.customer_area_desc) as customer_area_desc ,                                         -- 客户所属区县的名字',
      nvl(a.month_customer_waybill_sum,0) as month_customer_waybill_sum ,                                            --本月运单总量
      nvl(a.customer_signed_time,b.customer_signed_time) as customer_signed_time,                                    --客户创建时间
     if(a.customer_signed_time>=trunc('{{ execution_date | cst_ds }}','MM') and a.customer_signed_time<='{{ execution_date | cst_ds }}' and nvl(a.month_customer_waybill_sum,0)>0 ,1,0)as is_new,    --是否为新增
     if(a.customer_signed_time>=trunc('{{ execution_date | cst_ds }}','MM') and a.customer_signed_time<='{{ execution_date | cst_ds }}' and nvl(a.month_customer_waybill_sum,0)>0 ,nvl(a.month_customer_waybill_sum,0) ,0)as new_customer_waybill_sum,  --是否为新增
     case when a.ordersource_name is null  and b.ordersource_name is not null then 1
           else 0 end as is_loss,                                                                                    --是否流失
    case when  a.ordersource_name is null  and b.ordersource_name is not null then b.month_customer_waybill_sum
          else 0 end as loss_waybill_sum,                                                                            --流失总运单量
    case when a.customer_signed_time<trunc('{{ execution_date | cst_ds }}','MM') and  a.ordersource_name is not null  and b.ordersource_name is null then 1
         else 0 end as is_retrieve,                                                                                  --是否挽回
    case when a.customer_signed_time<trunc('{{ execution_date | cst_ds }}','MM') and a.ordersource_name is not null  and b.ordersource_name is null then nvl(a.month_customer_waybill_sum,0)
          else 0 end as retrieve_waybill_sum,                                                                        --挽回总运单量
       a.month_customer_weight_sum,
       b.month_customer_waybill_sum as last_month_customer_waybill_sum,
       b.month_customer_weight_sum as last_month_customer_weight_sum,
 '{{ execution_date | cst_ds }}' as dt
  from (
   select
      customer_code,                                                            --客户编码
      max(customer_name) as customer_name,                                      --客户名字
     concat_ws(',',collect_set(ordersource_code)) as ordersource_code,                                                      --平台ID
     ordersource_name,                                                      --平台名字
     max(customer_type_id) as customer_type_id  ,                                                    -- 客户类型
     max(customer_type_name) as customer_type_name,                                                   --客户类型名字
     max(customer_category) as customer_category,                                                 --客户类别
     max(customer_category_name) as customer_category_name,
     max(customer_attribute_id)as customer_attribute_id ,                                                   --客户属性ID
     max(customer_attribute_name) as customer_attribute_name,
     max(subordinate_agent_code) as subordinate_agent_code,                                       --所属代理区code
max(subordinate_agent_name) as subordinate_agent_name,                                       --所属代理区
     max(first_franchisee_id) as first_franchisee_id    ,                                       -- 一级加盟商id',
     max(first_franchisee_code) as first_franchisee_code,                                        -- '一级加盟商code',
     max(first_franchisee_name) as first_franchisee_name  ,                                                    -- 一级加盟商',
     max(customer_belong_network_id) as customer_belong_network_id ,                                                 -- 客户所属网点ID
     max(customer_belong_network_name) as customer_belong_network_name ,                                               --  客户所属网点名字
     max(customer_province_id) as customer_province_id   ,                                                     -- 客户所属省份id
max(customer_province_desc) as customer_province_desc ,                                                     --客户所属省份名字
     max(customer_city_id) as customer_city_id     ,                                                       -- 客户所属城市id
max(customer_city_desc) as customer_city_desc,                                                          --  客户所属城市名字',
     max(customer_area_id) as customer_area_id   ,                                                         --  客户所属区域id
max(customer_area_desc) as customer_area_desc ,                                                         -- 客户所属区县的名字',
sum(day_customer_sum) as month_customer_waybill_sum ,                                                           --运单量
sum(day_customer_weight_sum)  as month_customer_weight_sum,
min(customer_signed_time) as customer_signed_time                                                        --客户创建时间
  from  jms_dm.dm_customer_base_detail_dt
  where dt >= trunc('{{ execution_date | cst_ds }}','MM')
  and dt <='{{ execution_date | cst_ds }}'
  and day_customer_sum>0
  group by customer_code,ordersource_name
  )   a
  full join  last_month_data b
  on a.customer_code=b.customer_code
  and a.ordersource_name=b.ordersource_name
  where (a.ordersource_name is not null or b.ordersource_name is not null)
) a
left join  year_customer_sum b
  on a.customer_code=b.customer_code
  and a.ordersource_name=b.ordersource_name
  left join day_customer_sum c
  on a.customer_code=c.customer_code
  and a.ordersource_name=c.ordersource_name
  distribute by 10;